﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace 生产库导入正式库工具
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();

            var list = SGArchiveSystem7xDB.Linq.T_ArchiveTreeDal.Get_ArchiveTree();
            listView_ArchiveTree.Items.Clear();
            if (list == null)
                MessageBox.Show("加载档案类型错误!");
            else
            {
                foreach(var n in list)
                {
                    ListViewItem item = new ListViewItem(new string[] { n.AR_NAME }, 0);
                    item.Tag = n;
                    listView_ArchiveTree.Items.Add(item);
                }
            }
        }

        private void tsb_LoadWorkXls_Click(object sender, EventArgs e)
        {
            OpenFileDialog of = new OpenFileDialog();
            of.Filter = "*.xls|*.xls|*.xlsx|*.xlsx";
            if (of.ShowDialog() == DialogResult.OK)
            {
                listView_Work.Items.Clear();
                Aspose.Cells.Workbook work = new Aspose.Cells.Workbook(of.FileName);
                Aspose.Cells.Worksheet sheet = work.Worksheets[0];
                Aspose.Cells.Cells cells = sheet.Cells;

                listView_Work.Items.Clear();
                for(int i=1;i<cells.Rows.Count;i++)
                {
                    if (cells[i, 1].Value == null || cells[i, 1].Value.ToString() == "")
                        break;
                    else
                    {
                        string dh = cells[i, 1].Value.ToString();
                        string tag = (cells[i, 2].Value == null ? "" : cells[i, 2].Value.ToString());
                        listView_Work.Items.Add(new ListViewItem(new string[] { dh, tag }, 0));
                    }
                }
            }
        }

        private void tsb_AddData_Click(object sender, EventArgs e)
        {
            if(listView_ArchiveTree.SelectedItems.Count == 0)
            {
                MessageBox.Show("请先选择档案类型!");
                return;
            }

            OpenFileDialog of = new OpenFileDialog();
            of.Multiselect = true;
            of.Filter = "*.xls|*.xls|*.xlsx|*.xlsx";
            if (of.ShowDialog() == DialogResult.OK)
            {
                listView_Data.Items.Clear();
                foreach (var file in of.FileNames)
                {
                    Aspose.Cells.Workbook work = new Aspose.Cells.Workbook(file);
                    Aspose.Cells.Worksheet sheet = work.Worksheets[0];
                    Aspose.Cells.Cells cells = sheet.Cells;

                    int[] ints = new int[listView_Data.Columns.Count];
                    for (int i = 0; i < ints.Length; i++)
                        ints[i] = -1;
                    for(int i=0;i<listView_Data.Columns.Count; i++)
                    {
                        ints[i] = 0;
                        string headertext = listView_Data.Columns[i].Text;
                        if (headertext == "标签号")
                            continue;
                        for(int j = 0; j< 30; j++)
                        {
                            string h = (cells[1, j].Value == null ? "" : cells[1, j].Value.ToString());
                            if (headertext == h)
                            {
                                ints[i] = j;
                                break;
                            }
                        }
                        if(ints[i] == 0)
                        {
                            MessageBox.Show(headertext + " 在 " + file.Substring(file.LastIndexOf("\\") + 1) + " 中没有找到对应的列，请检查!");
                            return;
                        }
                    }

                    for (int i = 2; i < cells.Rows.Count; i++)
                    {
                        if (cells[i, 1].Value == null || cells[i, 1].Value.ToString() == "")
                            break;
                        else
                        {
                            ListViewItem item = new ListViewItem(new string[ints.Length + 1]);
                            for(int j=0; j< ints.Length; j++)
                            {
                                if(ints[j] > -1)
                                    item.SubItems[j].Text = (cells[i,  ints[j]].Value == null ? " " : cells[i, ints[j]].Value.ToString());
                            }
                            item.SubItems[ints.Length - 1].Text = " ";
                            item.BackColor = Color.Yellow;
                            listView_Data.Items.Add(item);
                           
                           //listView_Work.Items.Add(new ListViewItem(new string[] { dh, tag }, 0));
                        }
                    }
                }
            }
        }

        private void listView_ArchiveTree_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (listView_ArchiveTree.SelectedItems.Count == 1)
            {
                listView_Data.Columns.Clear();
                listView_Data.Items.Clear();
                var n = listView_ArchiveTree.SelectedItems[0].Tag as SGArchiveSystem7xDB.T_ArchiveTree;
                 ///获取档案字段
                var list = SGArchiveSystem7xDB.Linq.T_ArchiveFieldsDal.Get_ArchiveFieldsForArchive(n.AR_ID);
                foreach(var en in list)
                {
                    if(en.AF_SHOW == 1)
                    {
                        ColumnHeader header = new ColumnHeader();
                        header.Tag = en;
                        header.Text = en.AF_NAME;
                        listView_Data.Columns.Add(header);
                    }
                }
                ColumnHeader header2 = new ColumnHeader();
                header2.Tag = "标签号";
                header2.Text = "标签号";
                listView_Data.Columns.Add(header2);
            }
        }

        private void tsb_Pi_Click(object sender, EventArgs e)
        {
            if (listView_Data.Items.Count == 0 || listView_Work.Items.Count == 0)
                MessageBox.Show("请先加载数据！");
            else
            {
                System.Threading.Thread th = new System.Threading.Thread(Th);
                th.Start();
            }
        }

        private void Th()
        {
            CheckForIllegalCrossThreadCalls = false;
            pb.Maximum = listView_Work.Items.Count;
            pb.Minimum = 0;
            pb.Value = 0;
            for (int i = 0; i < listView_Work.Items.Count; i++)
            {
                string dh = listView_Work.Items[i].SubItems[0].Text;
                string tag = listView_Work.Items[i].SubItems[1].Text;
                for (int j = 0; j < listView_Data.Items.Count; j++)
                {
                    if (listView_Data.Items[j].SubItems[0].Text == dh)
                    {
                        listView_Data.Items[j].SubItems[listView_Data.Columns.Count - 1].Text = tag;
                        listView_Data.Items[j].BackColor = Color.White;
                        break;
                    }
                }
                pb.Value = i + 1;
            }
            MessageBox.Show("匹配完成!");
        }

        private void tsb_Import_Click(object sender, EventArgs e)
        {
            System.Threading.Thread th = new System.Threading.Thread(ThImport);
            th.Start();
        }

        private void ThImport()
        {
            CheckForIllegalCrossThreadCalls = false;
            pb.Maximum = listView_Data.Items.Count;
            pb.Minimum = 0;
            pb.Value = 0;
            for (int i = 0; i < listView_Data.Items.Count; i++)
            {
                if (listView_Data.Items[i].BackColor != Color.White)
                {
                    if (MessageBox.Show("有档案数据没有匹配到标签，是否仍然要导入系统!", "提示",
                        MessageBoxButtons.OKCancel, MessageBoxIcon.Question) == DialogResult.Cancel)
                        return;
                }
            }

            var bgqx = SGArchiveSystem7xDB.Linq.T_ArchiveWordDal.Get_Word("保管期限");


            SGArchiveSystem7xDB.SGArchiveSystem7xEntities conn = new SGArchiveSystem7xDB.SGArchiveSystem7xEntities();
            System.Data.SqlClient.SqlConnection sqlconn = new System.Data.SqlClient.SqlConnection();
            sqlconn.ConnectionString = conn.Database.Connection.ConnectionString;
            System.Data.SqlClient.SqlCommand cmmd;
            try
            {
                sqlconn.Open();
            }
            catch { MessageBox.Show("SQL连接数据库失败！"); return; }

            DateTime dt = DateTime.Now;
            var archiveTree = listView_ArchiveTree.SelectedItems[0].Tag as SGArchiveSystem7xDB.T_ArchiveTree;
            SGArchiveSystem7xDB.V_User user = new SGArchiveSystem7xDB.V_User();
            user.U_ID = 0;
            user.U_NAME = "导入工具";
            for (int i = 0; i < listView_Data.Items.Count; i++)
            {
                try
                {
                    bool bOK = true;
                    SGArchiveSystem7xDB.T_ArchiveAj t_ArchiveAj = new SGArchiveSystem7xDB.T_ArchiveAj();
                    string tag = listView_Data.Items[i].SubItems[listView_Data.Columns.Count - 1].Text;
                    if (tag.Trim() == "")
                        break;

                    string sql = "insert into t_archiveaj (arid, gdbm, mj, kfzt, ajzt, sjjlr, sjjllx, sjjlrq, i05, dzwj,";
                    string sql2 = "values(" + archiveTree.AR_ID.ToString() + ",1,6,1,1,'1','1','" + dt.ToString("yyyy-MM-dd HH:mm:ss") +
                        "', 1,'" + tag + "',";

                    ///用于诉讼 的特殊处理
                    string TM = "", AY = "";
                    for (int j = 0; j < listView_Data.Columns.Count - 1; j++)
                    {
                        var n = listView_Data.Columns[j].Tag as SGArchiveSystem7xDB.T_ArchiveFields;
                        sql += n.AF_FIELD + ",";

                      

                        var value = listView_Data.Items[i].SubItems[j].Text;
                        if (n.AF_FIELD == "BGQX")
                        {
                            bool b = false;
                            foreach (var bg in bgqx)
                            {
                                if (bg.WD_NAME == value)
                                {
                                    b = true;
                                    value = bg.WD_ID.ToString();
                                    break;
                                }
                            }
                            if (!b)
                                value = "0";
                        }
                        else if (n.AF_FIELD == "TM")
                            TM = value;
                        else if (n.AF_FIELD == "GJZ")
                            AY = value;

                        sql2 += "'" + value + "',";

                        if (n.AF_FIELD == "DH")
                        {
                            ///如果是档号，那么进行
                            string key = listView_Data.Items[i].SubItems[j].Text;
                            if (conn.T_ArchiveAj.Select(c => c).Where(c => c.DH == key).Count() > 0)
                            {
                                bOK = false;
                                break;
                            }
                        }
                    }
                    if (!bOK)
                    {
                        listView_Data.Items[i].BackColor = Color.Gray;
                        continue;
                    }

                    if (sql.EndsWith(","))
                        sql = sql.Substring(0, sql.LastIndexOf(","));
                    if (sql2.EndsWith(","))
                        sql2 = sql2.Substring(0, sql2.LastIndexOf(","));

                    if (archiveTree.AR_NAME == "诉讼档案")
                    {
                        sql = sql + ",V01)";
                        sql2 = sql2.Replace(TM, "案由:" + AY + " 被告人:" + TM);
                        sql2 = sql2 + ",'" + TM + "')";
                        sql = sql + " " + sql2;
                    }
                    else
                        sql = sql + ") " + sql2 + ")";
                    cmmd = new System.Data.SqlClient.SqlCommand(sql, sqlconn);
                    if (cmmd.ExecuteNonQuery() > 0)
                        listView_Data.Items[i].BackColor = Color.White;
                    else
                        listView_Data.Items[i].BackColor = Color.Red;
                }
                catch { listView_Data.Items[i].BackColor = Color.Red; }
                finally
                {
                    pb.Value = i + 1;
                }
            }
            MessageBox.Show("导入完成!");
        }

        private void tsb_ExpErr_Click(object sender, EventArgs e)
        {
            if (listView_Data.Items.Count == 0)
                return;
            else
            {
                SaveFileDialog sa = new SaveFileDialog();
                sa.Filter = "*.xls|*.xls";
                if(sa.ShowDialog() == DialogResult.OK)
                {
                    Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
                    Aspose.Cells.Worksheet worksheet = workbook.Worksheets[0];
                    Aspose.Cells.Cells cells = worksheet.Cells;
                    int index = 0;
                    cells[0, 0].Value = "档号";
                    cells[0, 1].Value = "错误类型";
                    index++;
                    for(int i=0;i<listView_Data.Items.Count;i++)
                    {
                        if (listView_Data.Items[i].BackColor == Color.Yellow)
                        {
                            cells[index, 0].Value = listView_Data.Items[i].SubItems[0].Text;
                            cells[index, 1].Value = "无标签信息";
                            index++;
                        }
                        else if (listView_Data.Items[i].BackColor == Color.Red)
                        {
                            cells[index, 0].Value = listView_Data.Items[i].SubItems[0].Text;
                            cells[index, 1].Value = "导入失败";
                            index++;
                        }
                    }
                    workbook.Save(sa.FileName);
                }
            }
        }
    }
}
